﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleDatabaseAdapter
{
    internal class DatabaseAccess
    {
        private string _SQLConnectionString;

        /// <summary>
        /// Create new DatabaseAccess instance
        /// </summary>
        /// <param name="sqlConnectionString">SQL connection string</param>
        internal DatabaseAccess(string sqlConnectionString)
        {
            _SQLConnectionString = sqlConnectionString;
        }

        /// <summary>
        /// Return data from sql
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <returns>Data</returns>
        internal DataTable GetData(string query)
        {
            if (!string.IsNullOrEmpty(query))
            {
                using (SqlConnection connection = new SqlConnection(_SQLConnectionString))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Connection.Open();

                    SqlDataAdapter reader = new SqlDataAdapter(command);
                    DataTable data = new DataTable();
                    reader.Fill(data);

                    return data;
                }
            }
            return null;
        }

        /// <summary>
        /// Return data from sql
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <param name="parametrs">SQL parametrs</param>
        /// <returns>Data</returns>
        internal DataTable GetData(string query, SqlParameter[] parametrs)
        {
            if (!string.IsNullOrEmpty(query))
            {
                using (SqlConnection connection = new SqlConnection(_SQLConnectionString))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Connection.Open();
                    command.Parameters.AddRange(parametrs);

                    SqlDataAdapter reader = new SqlDataAdapter(command);
                    DataTable data = new DataTable();
                    reader.Fill(data);

                    return data;
                }
            }
            return null;
        }

        /// <summary>
        /// Return items from table alldata
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <param name="parametrs">SQL parametrs</param>
        /// <param name="itemType">AllData item type, based on AllDataItemBase</param>
        /// <param name="columns">List columns</param>
        /// <returns>AllData items collection</returns>
        internal AllDataItemsCollection<T> ReadData<T>(string query, SqlParameter[] parametrs, List<ListColumnBase> columns)
        {
            if (!string.IsNullOrEmpty(query))
            {
                SqlConnection connection = new SqlConnection(_SQLConnectionString);
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Connection.Open();
                    command.Parameters.AddRange(parametrs);

                    SqlDataReader reader = command.ExecuteReader();
                    AllDataItemsCollection<T> items = new AllDataItemsCollection<T>(reader, columns);
                    return items;
                }
            }
            return null;
        }

        /// <summary>
        /// Execute query on sql and return rows affected
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <returns>Rows Affected</returns>
        internal int ExecuteQuery(string query)
        {
            if (!string.IsNullOrEmpty(query))
            {
                using (SqlConnection connection = new SqlConnection(_SQLConnectionString))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Connection.Open();

                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
            }
            return -1;
        }

        /// <summary>
        /// Execute query on sql and return scalar value
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <returns>Scalar value</returns>
        internal object ExecuteScalar(string query)
        {
            if (!string.IsNullOrEmpty(query))
            {
                using (SqlConnection connection = new SqlConnection(_SQLConnectionString))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Connection.Open();

                    object value = command.ExecuteScalar();
                    return value;
                }
            }
            return null;
        }

        /// <summary>
        /// Execute query on sql and return long value
        /// </summary>
        /// <param name="query">SQL query</param>
        /// <returns>Long value</returns>
        internal long ExecuteScalarLong(string query)
        {
            object value = ExecuteScalar(query);

            if (value != null)
            {
                long longValue;
                if (long.TryParse(value.ToString(), out longValue))
                {
                    return longValue;
                }
            }

            return -1;
        }
    }
}
